import os
import openpyxl
# 获取当前目录
cur_path = os.getcwd()

# print(cur_path)

def deal_excel(outfilename):
    data_path = os.path.join(cur_path,"data")

    filelists = os.listdir(data_path)

    # 创建新的保存汇总文件的xlsx
    wb_new = openpyxl.Workbook()
    # print(wb_new.get_sheet_names())
    wb_new_sheet_name = wb_new.get_sheet_names()[0]
    if wb_new_sheet_name != None:
        wb_new_sheet = wb_new.get_sheet_by_name(wb_new_sheet_name)
    else:
        wb_new_sheet = wb_new.create_sheet("sheet")

    # 判断是否在汇总文件中加入头信息，保证只加入一次
    flag_add_header = False
    # 从所有文件中对每个文件进行处理
    for file in filelists:

        file_path = os.path.join(data_path,file)
        if os.path.isfile(file_path):
            # print(file_path)
            wb = openpyxl.load_workbook(file_path)
            # print(wb.get_sheet_names())
            sheets = wb.get_sheet_names()
            st = wb.get_sheet_by_name(sheets[0])
            # 保存xlsx的header
            if not flag_add_header:
                wb_new_sheet.append([x.value for x in st['1']])
                wb_new_sheet.append([x.value for x in st['2']])
                flag_add_header = True
            # 从第三行还是判断每一行是否合格，然后存储
            # 假设每个文件只有前10行有信息
            row  = 3
            while row<10 :
                # 获取姓名cell的value值
                val = st[str(row)][1].value
                # 通过判断姓名是否合格进行加入汇总文件
                if val != None and val != "xxx" and val !="XXX":
                    # for i in range(st.max_column):
                    #     print(st[str(col)][i].value)
                    print(st[str(row)][1].value)
                    wb_new_sheet.append([x.value for x in st[str(row)]])
                row = row +1
        else:
            continue
    wb_new.save(outfilename)


def test():
    wb_new = openpyxl.Workbook()

    path = r"E:\VSworkspace\C_learn\excelconvert\data\学生去向登记表 (1).xlsx"
    wb = openpyxl.load_workbook(path)
    sheets = wb.get_sheet_names()
    st = wb.get_sheet_by_name(sheets[0])
    print(st['3'][1].value)
    col  = 1
    wb_new_sheet = wb_new.create_sheet("sheet1")
    while col<10 :
        val = st[str(col)][1].value
        if val != None and val != "xxx" and val != "姓名":
            # for i in range(st.max_column):
            #     print(st[str(col)][i].value)
            wb_new_sheet.append([x.value for x in st[str(col)]])
        col = col +1
    wb_new.save('all_test.xlsx')

deal_excel(outfilename='all2.xlsx')